<?php
	class Fulfillment extends Model_Abstract{
		
		private $_cons = '';
		private $_vars = array();
		private $_limit = 10;
				
		public function getCount(){
			try
			{
				$sql = "select count(*) as total from fulfillments where";
				if(!count($this->_cons)){
					$pos = strpos($sql, 'where');
					$sql =  substr($sql, 0, $pos-1);
				}elseif (count($this->_cons)>1){
					$cons = implode(' and', $this->_cons);
				}
				$result = $this->_connection->query($sql . $this->_cons, $this->_vars);
				if($result)
					return $result[0]['total'];
				return false;
			}
			catch (Exception $e)
			{
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function search($field = null, $condition = null, $page = 1, $onlyPending = true){
			$sqlCount = "SELECT count(*) AS total FROM fulfillments f INNER JOIN users u ON f.uid=u.id";
			$sql = "SELECT u.id AS uid, f.id, concat(u.firstname, ' ', u.lastname) AS name, f.bill, f.status, f.created, f.updated FROM fulfillments f INNER JOIN users u ON f.uid=u.id";
		
			$limit = is_null(Q::ini('appini/items_per_page')) ?  $this->_limit : Q::ini('appini/items_per_page');
			$constraint = " LIMIT " .($page-1)*$limit . ", $limit";
			$cons = '';
			if('user_id' == $field){
				$cons .= " u.id=?";
			}else if('status' == $field){
				$cons .= " f.status=?";
			}else{
				if ('email' == $field){
					$cons .= " u.email like ?";
				}else if ('firstname' == $field){
					$cons .= " u.firstname like ?";
				}else{
					$cons .= " u.lastname like ?";
				}
				$condition = "%" . trim($condition) . "%";
			}
			$vars = array($condition);
			if($onlyPending){
				$cons .= " AND f.status=0";
			}
			
			try
			{
				$result = $this->_connection->query($sqlCount . ' WHERE '.$cons, $vars);
				$totalCount = $result[0]['total'];
				
				$sql .= ' WHERE '.$cons . $constraint;
				$result = $this->_connection->query($sql, $vars);
				$result['totalCount'] = $totalCount;
				return $result;
			}
			catch (Exception $e)
			{
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function cancel($fid){
			$sql = "select * from fulfillments where id=? and status=0";
			try
			{
				$result = $this->_connection->query($sql, array($fid));
				if($result){
					$usermodel = new User();
					$this->_connection->beginTransactionW();
					if($usermodel->cancelFulfillment($result[0]['uid'], $result[0]['bill'])
						&& $this->setCanceled($fid)){
							$this->_connection->commitW();
							return true;
					}
				}
				$this->_connection->rollBackW();
				return false;
			}
			catch (Exception $e)
			{
				$this->_connection->rollBackW();
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function complete($fid){
			$sql = "select f.bill, u.site_id from fulfillments as f inner join users as u on f.uid=u.id  where f.id=? and (f.status=0 || f.status=3)";
			try
			{
				$result = $this->_connection->query($sql, array($fid));
				if($result){
					$sitemodel = new Site();
					$this->_connection->beginTransactionW();
					if($sitemodel->addToPaid($result[0]['bill'], $result[0]['site_id'])
						&& $this->setCompleted($fid)){
							$this->_connection->commitW();
							return true;
					}
				}
				$this->_connection->rollBackW();
				return false;
			}
			catch (Exception $e)
			{
				$this->_connection->rollBackW();
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function checkReturned($fid){
			try
			{
				$this->_connection->beginTransactionW();
				if($this->setCheckReturned($fid)){
					$this->_connection->commitW();
					return true;
				}
				$this->_connection->rollBackW();
				return false;
			}
			catch (Exception $e)
			{
				$this->_connection->rollBackW();
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		private function setCanceled($fid){
			$now = time();
			$sql = "update fulfillments set status=2, updated=? where id=? and status=0";
			try
			{
				$result = $this->_connection->execute($sql, array($now, $fid));
				return $result;
			}
			catch (Exception $e)
			{
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		private function setCompleted($fid){
			$now = time();
			$sql = "update fulfillments set status=1, updated=? where id=? and (status=0 || status=3)";
			try
			{
				return $this->_connection->execute($sql, array($now, $fid));
			}
			catch (Exception $e)
			{
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		private function setCheckReturned($fid){
			$now = time();
			$sql = "update fulfillments set status=3, updated=? where id=? and status=1";
			try
			{
				return $this->_connection->execute($sql, array($now, $fid));
			}
			catch (Exception $e)
			{
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function getPendingFulfillments(){
			$sql = "select count(*) as amount from fulfillments where status=0";
			try
			{
				$result = $this->_connection->query($sql);
				if($result)
					return $result[0]['amount'];
				return null;
			}
			catch (Exception $e)
			{
				$this->_connection->rollBackW();
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function getUserInfo($uid){
			$sql = "SELECT u.id, u.password, u.cam, u.pub, u.pla, u.confirmed, u.email, u.firstname, u.lastname, u.address1, u.address2, u.city, u.state, u.zipcode, u.phone, u.birthday, 
						   u.gender, u.created, u.last_visit, s.domain, u.credit, u.played, u.won, u.balance, u.unsub, count(r.id) as offer  
					FROM users AS u left join rewards AS r ON u.id=r.user_id left join sites AS s ON u.site_id=s.id 
					WHERE u.id=? GROUP BY u.id";
			try
			{
				return $this->_connection->query($sql, array($uid));
			}
			catch (Exception $e)
			{
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function getRewardsInfo($uid){
			$sql = "SELECT created AS rtime, name, credit as token 
					FROM rewards  
					WHERE user_id=? ";
			try
			{
				return $this->_connection->query($sql, array($uid));
			}
			catch (Exception $e)
			{
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
	}